package com.leadbank.springboot.example.dal.domain;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * Created by hp on 2018/1/10.
 */
@Mapper
public interface ProductMapper {

    @Select("select\n" +
            "  prd.product_code product_code,/*产品代码*/\n" +
            "  prd.PRODUCT_ABBREVIATION product_name,/*产品名称*/\n" +
            "  case prd.product_type2 when '501' then 'SYPZ' else 'LDB' end product_type,\n" +
            "  case mkr.mkt_identify when '1' then '9' when '2' then '2' when '3' then '9' else '9' end user_level, /*用户分层标签(1:不限制/2:新客/3:老客)*/\n" +
            "  mkt.annual_yield_rate profit_rate,/*收益率*/\n" +
            "  prd.profit_rate_type profit_rate_type,/*收益率类型(1:7日年化收益率/2:预期年化收益率/3:历史年化收益率/4:年化收益率)*/\n" +
            "  prd.invest_period invest_period,/*产品期限*/\n" +
            "  prd.product_status,/*购买状态(利得宝  1:待确认/2:可售/3:售罄 收益凭证 1:待确认/2:可售/3:售罄/4:销售结束)*/\n" +
            "  rb.invest_amt_min invest_amt_min, /*起购金额*/\n" +
            "  prd.sale_start_time sale_start_time,/*开售时间*/\n" +
            "  prd.sale_end_time sale_end_time,/*结束销售时间*/\n" +
            "  mkr.mkt_identify_desc market_label1, /*营销标题*/\n" +
            "  mkr.rule_desc1 market_label2 /*推介标题*/\n" +
            "from FTS_FAC.fts_prd_trade prd\n" +
            "left join FTS_FAC.fts_prd_trade_mkt mkt on mkt.product_code = prd.product_code and mkt.is_valid='Y'\n" +
            "left join FTS_FAC.fts_prd_mkt_rule mkr on mkr.product_mkt_rule_id = prd.product_mkt_rule_id and mkr.is_valid='Y'\n" +
            "left join FTS_FAC.fts_trade_rule_product rp on rp.product_code = prd.product_code and rp.is_valid='Y'\n" +
            "left join FTS_FAC.fts_trade_rule_buy rb on rb.buy_rule_id = rp.buy_rule_id and rb.is_valid='Y'\n" +
            "where (prd.product_type1='2' and prd.product_type2 in ('1','4','5','6')) or (prd.product_type1 in ('2','5') and prd.product_type2 in ('501'))\n" +
            "and prd.product_status <> '1'\n" +
            "and prd.UPDOWN_SALE_FLAG <> '2'")
    List<Product> getLdbProductList();

}
